if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[RptCSCRActivityInvestigationalProductReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[RptCSCRActivityInvestigationalProductReport]

GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =======================================================================
-- Procedure Name: dbo.RptCSCRActivityInvestigationalProductReport
--
-- Author:      Arthur A. Bianchini III
--
-- Create date: 4/18/2008
--
-- Description: Source of Report, "CSCR Activity Report"
--                  In Insight DB Reporting Services
--
--Purpose
--
-- Parameters:  
--     @StartDate                           
--     @EndDate                           
-- 6/1/2009 aab-- change code to codeid where pt.arr_name = 'PROP_TYPE' 
-- =======================================================================

CREATE PROCEDURE [dbo].[RptCSCRActivityInvestigationalProductReport]
(
	@CSCRExecutedStartDate				datetime,
	@CSCRExecutedEndDate				 	datetime,
	@DomainUserId								INT
)
 AS

BEGIN

	CREATE TABLE #TempCSCRActivityInvestigationalProduct

	(ProposalNumber									varchar(30) NULL, 
	 InvestigationalProduct							varchar(60) NULL,
	 CSCRExecutedStartDate						datetime NULL,
	 CSCRExecutedEndDate						datetime NULL,
	DomainUserId										INT NULL
	)

INSERT INTO #TempCSCRActivityInvestigationalProduct

SELECT DISTINCT
p.inst_no as ProposalNumber,
CASE u4.p_sin_30
	WHEN 'Drug/IND' THEN 'IND'
	WHEN 'Device/IDE' THEN 'IDE'
	WHEN 'Other/NA' THEN 'N/A'
END as InvestigationalProduct,
@CSCRExecutedStartDate,
@CSCRExecutedEndDate,
@DomainUserId
FROM proposal p
JOIN	PropPds pd
	ON	p.prop_no = pd.prop_no
	AND	p.inst_code = pd.inst_code
	AND pd.first_pd = 1
JOIN	prop_stat ps --aab 4/11/2008  select date change to CSCR - Executed
	on ps.prop_no = p.prop_no 
	and ps.inst_code = p.inst_code 
	and ps.prop_stat = (select codeid 
									from codetab 
									where arr_name = 'prop_stat' 
									and inst_code = 'partners'
									and code_desc = 'CSCR - Executed')
	AND	ps.stat_date = (SELECT MAX(ps1.stat_date)
									FROM	prop_stat ps1
									WHERE	ps.prop_no = ps1.prop_no
										AND	ps.inst_code = ps1.inst_code
										AND	ps.prop_stat = ps1.prop_stat
										AND	(ps.stat_date BETWEEN @CSCRExecutedStartDate AND DATEADD(day,1,@CSCRExecutedEndDate)))
							--AND	(ps.stat_date BETWEEN '09/01/2008' AND DATEADD(day,1,'09/14/2008')))
JOIN codetab pt
	on pt.codeid = p.prop_type 
	AND	pt.inst_code = p.inst_code
	and pt.arr_name = 'PROP_TYPE' 
	and pt.code_desc = 'Master'
JOIN Unit un 
	ON		p.inst_code = un.inst_code
	AND		p.unit_code = un.unit_code
JOIN Unit as inst
          ON    inst.inst_code = un.inst_code
          AND  left(un.deptid,4) = inst.deptID
          AND  len(inst.deptID) = 4
		 -- AND inst.int_unit_code in ('1200','2200','1400','FH','NWH','DFCI','DFPCC')
JOIN prop_u u2--get activity type
	ON p.inst_code = u2.inst_code 
	AND p.prop_no = u2.prop_no
	AND	u2.p_sin_14 = 'Clinical Research (C)'
JOIN prop_u u3-- get subactivity type
	ON p.inst_code = u3.inst_code 
	AND p.prop_no = u3.prop_no
	AND	(u3.p_sin_15 in  ('Trial - Jointly Initiated (C)','Trial - Other (C)','Trial - Company Initiated (C)','Trial - PI Initiated (C)')
	OR	u3.p_sin_15 like 'Compassionate Use%'
	OR	u3.p_sin_15 like 'Disease Mechanisms%')
LEFT JOIN		CodeTab f2
	ON	p.prop_stat = f2.codeID
	AND	p.inst_code = f2.inst_code
	AND f2.arr_name = 'PROP_STAT'
JOIN		prop_u u4--investigationalProduct
	ON	p.inst_code = u4.inst_code
	AND	p.prop_no = u4.prop_no
	AND	u4.p_sin_30 is not null and u4.p_sin_30 <> ''
JOIN INSIGHT_RPT_DB.dbo.fnGetSecurityAgreements (@DomainUserId) sec --updated on 1/28/09
	ON p.prop_no = sec.InfoEdPropNumber
WHERE len(p.inst_no) = 11
	AND		p.system = 'pt'
	AND		p.inst_code = 'partners'

CREATE TABLE #TempIP

(InvestigationalProduct							varchar(60) NULL,
 ProposalCount										INT NULL
)

INSERT INTO #TempIP

(InvestigationalProduct)

SELECT DISTINCT
CASE p_sin_30
	WHEN 'Drug/IND' THEN 'IND'
	WHEN 'Device/IDE' THEN 'IDE'
	WHEN 'Other/NA' THEN 'N/A'
END as InvestigationalProduct
FROM	prop_u 
WHERE p_sin_30 is not null and p_sin_30 <> ''

SELECT DISTINCT
COUNT(*) as ProposalCount,
InvestigationalProduct	
INTO #TempIPCount
FROM	#TempCSCRActivityInvestigationalProduct
GROUP BY InvestigationalProduct	
ORDER BY InvestigationalProduct	

UPDATE ip
SET	ip.ProposalCount = ipc.ProposalCount
FROM	#TempIPCount ipc
JOIN	#TempIP ip
	ON	ip.InvestigationalProduct = ipc.InvestigationalProduct

--Final Results for report

SELECT DISTINCT 
InvestigationalProduct,
IsNull(ProposalCount,0) as ProposalCount,
@CSCRExecutedStartDate as CSCRExecutedStartDate,
@CSCRExecutedEndDate as CSCRExecutedEndDate
FROM	#TempIP
ORDER BY InvestigationalProduct

END
GO



